/**
	DECLARE @STATUS VARCHAR(50) 
	EXEC SP_DELETE_PROCESS_STEP 2379, @STATUS OUT
	PRINT @STATUS
*/
ALTER PROC SP_DELETE_PROCESS_STEP ( @PROCESSSTEP_ID INT,
								@STATUS VARCHAR(50) OUT )
AS
BEGIN
	DECLARE @LOT_COUNT INT
	DECLARE @LINEITEM_COUNT INT	
	
	SELECT @LOT_COUNT = COUNT(*) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	SELECT @LINEITEM_COUNT = COUNT(*)  FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE COOL.PRODUCT_ID = P.PRODUCT_ID AND P.PROCESSSTEP_ID = @PROCESSSTEP_ID

	IF ( @LOT_COUNT > 0 )
	BEGIN
		SET @STATUS = 'LOT(S) EXISTS'
		RETURN
	END	
	ELSE IF ( @LINEITEM_COUNT > 0 )
	BEGIN
		SET @STATUS = 'SALES ORDER(S) EXISTS'
		RETURN
	END
	ELSE --CAN DELETE
	BEGIN
		DELETE FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		DELETE FROM PROCESS_STEP_TRAVERSE_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID OR NEXT_PROCESSSTEP_ID = @PROCESSSTEP_ID		
		DELETE FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		DELETE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
	END
	SET @STATUS = 'SUCCESS'
	RETURN
END